package vinux.web.common.es;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.collections.MapUtils;

import vinux.service.common.util.StringUtils;


public class BeanToSql {

	//javabean 
	public static String convert(Object clz) throws Exception{
		String  s = clz.getClass().getName();
		String tableName = s.substring((s.lastIndexOf(".") + 1)).toLowerCase();
		String sql = "insert into " + tableName;
		
		String attr = "(";
		String vallue = "(";
		
		
		
		PropertyDescriptor descriptor;
		String propertyName;
		BeanInfo beanInfo = Introspector.getBeanInfo(clz.getClass());
		PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
		
		int len= propertyDescriptors.length;
		for (int i = 0; i < len; i++) {
			descriptor = propertyDescriptors[i];
			propertyName = descriptor.getName();
			Object result = null;
			if ((!propertyName.equals("class")) && (!propertyName.equals("serialVersionUID"))) {
				Method readMethod = descriptor.getReadMethod();
				result = readMethod.invoke(clz, new Object[0]);
				if("id".equals(propertyName)){
					attr += propertyName + ",";
					if(null == result || "".equals(result.toString().trim())){
						vallue += "'" + UUID.randomUUID() + "',";
					}else{
						vallue += "'" + result.toString().trim() + "',";
					}
					continue;
				}
				
				if(null != result && !result.toString().trim().equals("") && propertyName != null){
					attr += propertyName;
					vallue += "'"+result+"'";
					if(len > (i+1)){
						attr += ",";
						vallue += ",";
					}
				}
			}
		}
		if(attr.substring(attr.lastIndexOf(",")).equals(",")){
			System.out.println(attr);
			attr = attr.substring(0, attr.lastIndexOf(","));
			System.out.println(vallue);
			vallue = vallue.substring(0, vallue.lastIndexOf(","));
		}
		sql += attr + ") values ";
		sql += vallue + ")";
		System.out.println(sql);
		return sql;
	}
	
	public static String update(Object clz) throws Exception{
		String  s = clz.getClass().getName();
		String tableName = s.substring((s.lastIndexOf(".") + 1)).toLowerCase();
		String sql = "update " + tableName + " set ";
		String id = "";
		PropertyDescriptor descriptor;
		String propertyName;
		BeanInfo beanInfo = Introspector.getBeanInfo(clz.getClass());
		PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
		
		int len= propertyDescriptors.length;
		for (int i = 0; i < len; i++) {
			descriptor = propertyDescriptors[i];
			propertyName = descriptor.getName();
			Object result = null;
			if ((!propertyName.equals("class")) && (!propertyName.equals("serialVersionUID"))) {
				Method readMethod = descriptor.getReadMethod();
				result = readMethod.invoke(clz, new Object[0]);
				if("id".equals(propertyName)){
					id = result.toString();
					if(null == id || "".equals(id.trim())){
						id = null;
						break;
					}else{
						continue;
					}
				}
				if(null != result && !result.toString().trim().equals("") && propertyName != null){
					sql += propertyName+ "='" + result + "',";
				}
			}
		}
		
		if(null != id){
			sql = sql.substring(0, sql.lastIndexOf(","));
			sql += " where id='" + id + "'";
			System.out.println(sql);
		}else{
			sql = null;
		}
		return sql;
	}
	
	public static String delete(Object clz, String id) throws Exception{
		String  s = clz.getClass().getName();
		String tableName = s.substring((s.lastIndexOf(".") + 1)).toLowerCase();
		String sql = "delete from " + tableName + "/"+ tableName + " where id='" + id+"'";
		return sql;
	}
	
	/**
	 * 
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static String select(Map<String, String> params) throws Exception{
		String tableName = null;//查询表名
		String customSql = null; //自定义查询条件
		String orderBy = null;//排序
		String groupBy = null;//分组
		String inQuery = null;//in查询
		String regionQuery = null;
		
		int pageNo = -1;//当前查询页
		int pageSize = 10;//每页查询条数
		
		//参数列表不能为空，至少包含表名
		if(MapUtils.isEmpty(params)){
			return null;
		}
		
		//判断参数中是否包含表名并且获取表名
		if(params.containsKey("tableName")){
			tableName = params.get("tableName");	
			params.remove("tableName");
		}else{
			return null;
		}
		
		//解析并且获取当前查询第几页
		if(params.containsKey("pageNo")){
			pageNo = Integer.parseInt(params.get("pageNo"));
			if(pageNo < 0){
				pageNo = 0;
			}
			params.remove("pageNo");
		}
		//每页查询条数
		if(params.containsKey("pageSize")){
			pageSize = Integer.parseInt(params.get("pageSize"));
			params.remove("pageSize");
		}
		
		//判断并且获取参数中排序参数,例：orderBy=a desc ,b asc
		if(params.containsKey("orderBy")){
			orderBy = params.get("orderBy");
			params.remove("orderBy");
		}
		
		//判断并且获取自定义查询参数
		if(params.containsKey("customSql")){
			customSql = params.get("customSql");
			params.remove("customSql");
		}
		//判断并且获取分组查询参数,例：groupBy=a,b
		if(params.containsKey("groupBy")){
			groupBy = params.get("groupBy");
			params.remove("groupBy");
		}
		
		//in(not in)查询
		if(params.containsKey("inQuery")){
			inQuery = params.get("inQuery");
			params.remove("inQuery");
		}
		
		// 区域查询 (between  .... and ....)
		if(params.containsKey("regionQuery")){
			regionQuery = params.get("regionQuery");
			params.remove("regionQuery");
		}
		
		
		//是否有条件，根据此变量删除sql中(1=1 and)或者 (where 1=1)
		boolean hasConditions = false;
		
		
		//聚合查询列
		String aggregationQuery = "";
		if(params.containsKey("aggregationQuery")){
			aggregationQuery = params.get("aggregationQuery");
			params.remove("aggregationQuery");
		}
		
		StringBuffer sql = new StringBuffer("select ");
		if(StringUtils.isNotBlank(aggregationQuery)){
			sql.append(aggregationQuery);
		}else{
			sql.append("*");
		}
		sql.append(" from ").append(tableName.toLowerCase()).append(" where 1=1");
		
		//解析其他表内字段查询参数
		if(MapUtils.isNotEmpty(params)){
			hasConditions = true;
			Set<String> ss = params.keySet();
			Iterator<String> it = ss.iterator();
			while (it.hasNext()) {
				String k = it.next();
				String v = params.get(k);
				sql.append(" and ").append(k).append("='").append(v).append("' ");
			}
		}
		
		if(null != customSql){
			hasConditions = true;
			sql.append(" ").append(customSql).append(" ");
		}
		
		
		if(null != inQuery){
			hasConditions = true;
			sql.append(" and ").append(inQuery).append(" ");
		}
		
		if(null != regionQuery){
			hasConditions = true;
			sql.append(" and ").append(regionQuery).append(" ");
		}
		
		
		if(null != groupBy){
			sql.append(" group by ").append(groupBy);
		}
		
		if(null != orderBy){
			sql.append(" order by ").append(orderBy);
		}
		if(pageNo > -1){
			sql.append(" limit ").append(pageNo).append(",").append(pageSize);
		}
		if(hasConditions){
			if(sql.indexOf("1=1 and") > 0){
				return sql.toString().replace("1=1 and", " ");
			}else if(sql.indexOf("1=1  and") > 0){
				return sql.toString().replace("1=1  and", " ");
			}else{
				return sql.toString();
			}
		}else{
			return sql.toString().replace("where 1=1", " ");
		}
	}
}
